package org.tnaggs.project.repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.tnaggs.project.model.Game;

public class GameRepository implements GenericRepository<Game> {
	
	
	private JdbcTemplate template;
	
	public GameRepository(JdbcTemplate template) {
		this.setTemplate(template);
	}

	public JdbcTemplate getTemplate() {
		return template;
	}

	public void setTemplate(JdbcTemplate template) {
		this.template = template;
	}

	@Override
	public Game findByID(int id) {
		return (Game)template.queryForObject("select * from Game where Game_ID = " + id, new GameRowMapper());
	}
	
	@Override
	public List<Game> findAll() {
		return (List<Game>)template.query("select * from Game", new GameRowMapper());
	}
	
	public List<Game> findByTeamID(long teamID) {
		return (List<Game>)template.query("select * from Game where Home_team = " + teamID + " or Away_team = " + teamID, new GameRowMapper());
	}
	
	class GameRowMapper implements RowMapper<Game> {

		@Override
		public Game mapRow(ResultSet rs, int arg1) throws SQLException {
		
				Game results = new Game();
				results.setGameID(rs.getInt("Game_ID"));
				results.setHomeTeamID(rs.getInt("Home_team"));
				results.setAwayTeamID(rs.getInt("Away_team"));
				results.setLocationID(rs.getInt("Location"));
				results.setGameDate(rs.getDate("Date"));
				results.setGameTime(rs.getTime("Time"));
				
				results.setHomeScore(rs.getInt("Home_score"));
				results.setAwayScore(rs.getInt("Away_score"));
				
				return results;
		}	
	}

	@Override
	public boolean insert(Game newVal) {
		
		Date gameDate = newVal.getGameDate();
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String gameDay = sdf.format(gameDate);
		
		sdf.applyPattern("HH:mm:ss");
		String time = sdf.format(gameDate);
		
		
		int numResults = template.update("insert into Game (`Home_team`,`Away_team`,`Location`,`Date`,`Time`,`Home_score`,`Away_score`) values(" 
			+ newVal.getHomeTeamID() + "," + newVal.getAwayTeamID() 
			+ "," + newVal.getLocationID() + ",'" + gameDay 
			+ "','" + time + "'," + newVal.getHomeScore() + "," + newVal.getAwayScore() + ")");
		return numResults == 1;
	}

	@Override
	public boolean delete(int id) {
		int numResults =  template.update("delete from Game where Game_ID = " + id);
		return numResults == 1;
	}

	@Override
	public boolean purge() {
      int numResults = template.queryForInt("select count(*) from Game;");
      int delResults = template.update("delete from Game;");
      return numResults == delResults;
	}

	public void updateScore(int gameID, int homeScore, int awayScore) {
		template.update("update Game set Home_score=" + homeScore + ", Away_score=" + awayScore + " where Game_ID=" + gameID);
	}

	public List<Game> findByLocalID(int localID) {
		return (List<Game>)template.query("select * from Game where Location = " + localID, new GameRowMapper());
	}
}
